﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

public class Conexion {
    public MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    //Constructor para conexion local
    public Conexion() {
        Inicializar();
    }

    //Constructor para conexion hacia servidor de base de datos remoto
    public Conexion(String strServidor, String strDbName, String strUsuario, String strPasswd) {
        Inicializar(strServidor, strDbName, strUsuario, strPasswd);
    }

    //Inicializador local
    private void Inicializar() {
        ////Server=localhost;Port=3306;Database=midb;Uid=root ;Pwd=pass;
        //server = "localhost:1234";
        //database = "srv_digital";
        //uid = "digibrain_user";
        //password = "admin";
        //string connectionString;
        //connectionString = "Server=" + server + ";Port=3306;" + "Database=" +
        //database + ";" + "Uid=" + uid + ";" + "Pwd=" + password + ";";
        //connection = new MySqlConnection(connectionString);
        MySqlConnectionStringBuilder conn_string = new MySqlConnectionStringBuilder();
        conn_string.Server = "localhost";
        conn_string.UserID = "root";
        conn_string.Password = "admin";
        conn_string.Database = "srv_digital";
        conn_string.Port = 3306;

        connection = new MySqlConnection(conn_string.ToString());
        //using (MySqlCommand cmd = conn.CreateCommand()) {    //watch out for this SQL injection vulnerability below
        //    cmd.CommandText = string.Format("INSERT Test (lat, long) VALUES ({0},{1})",
        //                                   OSGconv.deciLat, OSGconv.deciLon);
        //    connection.Open();
        //    cmd.ExecuteNonQuery();
        //}
    }

    //Inicializador Remoto
    private void Inicializar(String strServidor,
                             String strDbName,
                             String strUsuario,
                             String strPasswd,
                             String strPuerto = "-1") {
        if (strPuerto != "-1") {
            strServidor += ":" + strPuerto;
        }
        string connectionString;
        connectionString = "SERVER=" + strServidor + ";" + "DATABASE=" +
            strDbName + ";" + "UID=" + strUsuario + ";" + "PASSWORD=" + strPasswd + ";";
        connection = new MySqlConnection(connectionString);
    }

    //open connection to database
    public bool Open() {
        try {
            connection.Open();
            return true;
        } catch (MySqlException ex) {
            //When handling errors, you can your application's response based 
            //on the error number.
            //The two most common error numbers when connecting are as follows:
            //0: Cannot connect to server.
            //1045: Invalid user name and/or password.
            switch (ex.Number) {
                case 0:
                    throw new Exception("No se puedo conectar al servidor. Component:Conexion");
                case 1045:
                    throw new Exception("No se puedo conectar al servidor: Clave o Usuario incorrecto. Component:Conexion");
            }
            return false;
        }
    }


    //sss


    //Close connection
    public bool Close() {
        try {
            connection.Close();
            return true;
        } catch (MySqlException ex) {
            return false;
        }
    }


    //Insert statement
    public void Insert(String query) {


        //open connection
        if (this.Open() == true) {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.Close();
        }
    }

    public DataSet Select(String query) {
        //Open connection
        DataSet ds = new DataSet();
        if (this.Open() == true) {
            MySqlDataAdapter adr = new MySqlDataAdapter(query, connection);
            adr.SelectCommand.CommandType = CommandType.Text;
            adr.Fill(ds);
            return ds;
        } else {
            return null;
        }
    }

    public int Delete(String query) {
        if (this.Open() == true) {
            try {
                MySqlDataAdapter adr = new MySqlDataAdapter(query, connection);
                adr.DeleteCommand.CommandType = CommandType.Text;
                return 1;
            } catch (Exception ex) {
                throw new Exception(ex.ToString());
            }
        } else {
            return -1;
        }
    }
}